const dayjs = require('dayjs')
const db = require('../db/index')
const createExcel = require('../js/exceljs')
// 获取保险记录
exports.insuranceList = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  let sql = `select * from insurance where appid=? and status= 1 limit ${size} OFFSET  ${from}`
  let sql2 = `select count(*) from insurance where appid=? and status= 1`
  if (pageData.status == 0) {
    sql = `select * from insurance where appid=? and (status= 0 or status= 2 ) limit ${size} OFFSET  ${from}`
    sql2 = `select count(*) from insurance where appid=? and (status= 0 or status= 2 )`
  }
  db.query(sql, [appid, pageData.status], (err, res2) => {
    if (err) return res.cc(err)

    db.query(sql2, [appid], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}
// 获取车险详情
exports.insuranceDetils = (req, res) => {
  let data = req.body
  let sql = `select * from insurance where _id=?`
  db.query(sql, [data._id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      res: res2[0]
    })
  })
}
// 导出信息
exports.insuranceCreateExcel = (req, res) => {
  let data = req.body
  columns = [
    { header: '车险上传时间', key: 'create_time' },
    { header: '投保人姓名', key: 'carOwnerName' },
    { header: '身份证号', key: 'identity' },
    { header: '保单号', key: 'warrantyNum' },
    { header: '联系方式', key: 'carOwnerPhone' },
    { header: '所在区域', key: 'region' },
    { header: '车辆品牌型号', key: 'carName' },
    { header: '车牌号', key: 'carNo' },
    { header: '车架号', key: 'carVIN' },
    { header: '投保产品', key: 'filmName' },
    { header: '状态', key: 'status' },
    { header: '保险费', key: 'price' }
  ]
  let sql = ``
  if (data.id == 'all') {
    sql = 'select * from insurance where carOwnerId=? and status=1'
  } else {
    sql = 'select * from insurance where carOwnerId=?  and status=1 and _id in (?)'
  }
  db.query(sql, [data.carOwnerId, data.id], async (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      item.status = item.status == 0 ? '生效' : '失效'
      item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss')
    })
    const buffer = await createExcel.createExcel(columns, res2)
    res.setHeader('Content-disposition', 'attachment; filename=download.xlsx')
    res.setHeader('Content-type', 'application/octet-stream')
    res.send(buffer)
  })
}
// 审核
exports.insuranceChangeStatus = (req, res) => {
  let data = req.body
  let sql = `update insurance set status=? where _id=?`
  db.query(sql, [data.status, data._id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: "审核完成",
      type: 1
    })
  })
}
// 车险审核搜索
exports.insuranceSerach = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  pageData.filmNo = pageData.filmNo ? pageData.filmNo : ''
  pageData.carVIN = pageData.carVIN ? pageData.carVIN : ''
  pageData.warrantyNum = pageData.warrantyNum ? pageData.warrantyNum : ''
  pageData.filmName = pageData.filmName ? pageData.filmName : ''
  pageData.carName = pageData.carName ? pageData.carName : ''
  pageData.filmType = pageData.filmType ? pageData.filmType : ''
  pageData.status = pageData.status?.length > 0 ? pageData.status : ['0', '2']

  const sql = `select * from insurance where appid=? and(filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and warrantyNum like "%${pageData.warrantyNum}%" and filmName like "%${pageData.filmName}%"and carName like "%${pageData.carName}%" and filmType like "%${pageData.filmType}%" ) and status in (?) limit ${size} OFFSET  ${from}`
  db.query(sql, [appid, pageData.status], (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from insurance where appid=? and (filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and warrantyNum like "%${pageData.warrantyNum}%" and filmName like "%${pageData.filmName}%"and carName like "%${pageData.carName}%" and filmType like "%${pageData.filmType}%" ) and status in (?)`
    db.query(sql2, [appid, pageData.status], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}
// 添加车险
exports.insuranceAdd = (req, res) => {
  let data = req.body
  const sql = 'insert into insurance set ?'
  db.query(sql, data, (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '添加成功'
    })
  })
}
//小程序获取车险列表
exports.insuranceListSamll = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  if (pageData.carOwnerId) {
    let sql = `select * from insurance where appid=? and status in(?) and carOwnerId=? limit ${size} OFFSET  ${from}`
    let sql2 = `select count(*) from insurance where appid=? and status in(?) and carOwnerId=?`
    db.query(sql, [appid, pageData.status, pageData.carOwnerId], (err, res2) => {
      if (err) return res.cc(err)

      db.query(sql2, [appid, pageData.status, pageData.carOwnerId], (err2, res3) => {
        if (err2) return res.cc(err2)
        res.send({
          res: res2,
          total: res3[0]['count(*)']
        })
      })
    })
  } else {
    let sql = `select * from insurance where appid=? and status in(?) and storeId=? limit ${size} OFFSET  ${from}`
    let sql2 = `select count(*) from insurance where appid=? and status in(?) and storeId=?`
    db.query(sql, [appid, pageData.status, pageData.storeId], (err, res2) => {
      if (err) return res.cc(err)
      db.query(sql2, [appid, pageData.status, pageData.storeId], (err2, res3) => {
        if (err2) return res.cc(err2)
        res.send({
          res: res2,
          total: res3[0]['count(*)']
        })
      })
    })
  }

}
function pollDatabase () {
  // 执行数据库查询
  db.query('SELECT * FROM insurance', (error, results) => {
    if (error) {
      console.error('数据库查询出错:', error)
      return
    }
    // 处理查询结果
    results.forEach((item) => {
      if (dayjs(item.endTime).valueOf() < dayjs().valueOf()) {
        let sql = `update insurance set loseEfficacy=1 where _id=?`
        db.query(sql, [item._id], (err, res) => {

        })
      }
    })
  })
}

// 设置间隔（例如一天）
const oneDay = 24 * 60 * 60 * 1000 // 1天的毫秒数

// 开始定时轮询
setInterval(pollDatabase, oneDay)
